package gorm_util

import (
	"database/sql"
	"fmt"
	"github.com/pkg/errors"
	"gorm.io/gorm"
	"trpg-common/database"
	"trpg-common/logger"
)

// 使用paramBuilder 务必拼接model方法
// or方法的逻辑为or中的不同paramBuilder的条件会拼接为or，同一个paramBuilder的条件会拼接为and
// 例如 NewParamBuilder().Or(gorm_util.NewParamBuilder().Eq("id", id).Like("name", "%"+search+"%"), gorm_util.NewParamBuilder().Like("path", "%"+search+"%"))
// 将拼接为 and (id = id and name like %search%) or (path like %search%)
// or 内部继续拼接or的复杂sql请手写使用raw，不要使用builder

type params struct {
	eqMap    map[string]interface{}
	likeMap  map[string]string
	gtMap    map[string]interface{}
	gteMap   map[string]interface{}
	ltMap    map[string]interface{}
	lteMap   map[string]interface{}
	inMap    map[string]interface{}
	notEqMap map[string]interface{}
}

func newParams() *params {
	params := &params{
		eqMap:    map[string]interface{}{},
		likeMap:  map[string]string{},
		gtMap:    map[string]interface{}{},
		gteMap:   map[string]interface{}{},
		ltMap:    map[string]interface{}{},
		lteMap:   make(map[string]interface{}),
		inMap:    make(map[string]interface{}),
		notEqMap: make(map[string]interface{}),
	}
	return params
}

type ParamBuilder struct {
	model        interface{}
	selectFields string
	params       *params
	orParams     []*ParamBuilder
	orderByAsc   []string
	orderByDesc  []string
	limit        int
	offset       int
}

func NewParamBuilder() *ParamBuilder {
	paramBuilder := &ParamBuilder{}
	paramBuilder.model = nil
	paramBuilder.params = newParams()
	paramBuilder.orParams = make([]*ParamBuilder, 0)
	paramBuilder.orderByAsc = make([]string, 0, 5)
	paramBuilder.orderByDesc = make([]string, 0, 5)
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Model(value interface{}) *ParamBuilder {
	if value != nil {
		paramBuilder.model = value
	}
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Select(field string) *ParamBuilder {
	if paramBuilder.selectFields == "" {
		paramBuilder.selectFields += field
	} else {
		paramBuilder.selectFields += fmt.Sprintf(",%s", field)
	}
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Or(params ...*ParamBuilder) *ParamBuilder {
	paramBuilder.orParams = append(paramBuilder.orParams, params...)
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Eq(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.eqMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) NotEq(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.notEqMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Like(field string, value string) *ParamBuilder {
	paramBuilder.params.likeMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Gt(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.gtMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Lt(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.ltMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Gte(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.gteMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Lte(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.lteMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) In(field string, value interface{}) *ParamBuilder {
	paramBuilder.params.inMap[field] = value
	return paramBuilder
}

func (paramBuilder *ParamBuilder) OrderByDesc(field string) *ParamBuilder {
	paramBuilder.orderByDesc = append(paramBuilder.orderByDesc, field)
	return paramBuilder
}

func (paramBuilder *ParamBuilder) OrderByAsc(field string) *ParamBuilder {
	paramBuilder.orderByAsc = append(paramBuilder.orderByAsc, field)
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Order(field string, order string) *ParamBuilder {
	switch order {
	case "asc":
		paramBuilder.orderByAsc = append(paramBuilder.orderByAsc, field)
	case "desc":
		paramBuilder.orderByDesc = append(paramBuilder.orderByDesc, field)
	default:
		logger.Logger.Panic("error param")
	}
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Limit(limit int) *ParamBuilder {
	paramBuilder.limit = limit
	return paramBuilder
}

func (paramBuilder *ParamBuilder) Offset(offset int) *ParamBuilder {
	paramBuilder.offset = offset
	return paramBuilder
}

type PageResult struct {
	Pages       int32       `json:"pages"`
	Total       int64       `json:"total"`
	PageSize    int32       `json:"pageSize"`
	CurrentPage int32       `json:"currentPage"`
	List        interface{} `json:"list"`
}

type PageRequest struct {
	Page     int32 `json:"page" form:"page"`
	PageSize int32 `json:"pageSize" form:"pageSize"`
}

func getSqlAndParamFromParams(params *params) (string, []interface{}) {
	sql := ""
	values := make([]interface{}, 0)
	operation := "and"
	if len(params.eqMap) != 0 {
		for k, v := range params.eqMap {
			sql += fmt.Sprintf("%s = ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.inMap) != 0 {
		for k, v := range params.inMap {
			sql += fmt.Sprintf("%s in ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.likeMap) != 0 {
		for k, v := range params.likeMap {
			sql += fmt.Sprintf("%s like ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.gtMap) != 0 {
		for k, v := range params.gtMap {
			sql += fmt.Sprintf("%s > ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.ltMap) != 0 {
		for k, v := range params.ltMap {
			sql += fmt.Sprintf("%s < ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.gteMap) != 0 {
		for k, v := range params.gteMap {
			sql += fmt.Sprintf("%s >= ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.lteMap) != 0 {
		for k, v := range params.lteMap {
			sql += fmt.Sprintf("%s <= ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	sql = sql[0 : len(sql)-2-len(operation)]
	return sql, values
}

func setParamToDbConnection(db *gorm.DB, params *params) {
	sql := ""
	values := make([]interface{}, 0)
	operation := "and"
	if len(params.eqMap) != 0 {
		for k, v := range params.eqMap {
			sql += fmt.Sprintf("%s = ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.inMap) != 0 {
		for k, v := range params.inMap {
			sql += fmt.Sprintf("%s in ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.likeMap) != 0 {
		for k, v := range params.likeMap {
			sql += fmt.Sprintf("%s like ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.gtMap) != 0 {
		for k, v := range params.gtMap {
			sql += fmt.Sprintf("%s > ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.ltMap) != 0 {
		for k, v := range params.ltMap {
			sql += fmt.Sprintf("%s < ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.gteMap) != 0 {
		for k, v := range params.gteMap {
			sql += fmt.Sprintf("%s >= ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.lteMap) != 0 {
		for k, v := range params.lteMap {
			sql += fmt.Sprintf("%s <= ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if len(params.notEqMap) != 0 {
		for k, v := range params.notEqMap {
			sql += fmt.Sprintf("%s != ?", k) + " " + operation + " "
			values = append(values, v)
		}
	}
	if sql != "" {
		sql = sql[0 : len(sql)-2-len(operation)]
	}
	db.Where(sql, values...)
}

func setOrParamToDbConnection(db *gorm.DB, orParams []*ParamBuilder) {
	if orParams != nil && len(orParams) != 0 {
		totalSql := ""
		values := make([]interface{}, 0)
		for _, param := range orParams {
			sql, vals := getSqlAndParamFromParams(param.params)
			totalSql += "(" + sql + ") or "
			values = append(values, vals...)
		}
		totalSql = totalSql[0 : len(totalSql)-4]
		db.Where(totalSql, values...)
	}
}

func SelectByParamStruct(param interface{}, res interface{}) {
	db := database.GetDB()
	err := db.Where(param).Find(res).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
}

func PageSelectByParamBuilder(builder *ParamBuilder, res interface{}, request PageRequest) *PageResult {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	var total int64
	err := db.Count(&total).Error
	offset := (request.Page - 1) * request.PageSize
	db = db.Offset(int(offset))
	db = db.Limit(int(request.PageSize))
	err = db.Find(res).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
	pages := total / int64(request.PageSize)
	if total%int64(request.PageSize) != 0 {
		pages += 1
	}
	return &PageResult{
		Total:       total,
		Pages:       int32(pages),
		PageSize:    request.PageSize,
		CurrentPage: request.Page,
		List:        res,
	}
}

func SelectByParamBuilder(builder *ParamBuilder, res interface{}) {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	if builder.limit != 0 {
		db = db.Limit(builder.limit)
	}
	if builder.offset != 0 {
		db = db.Offset(builder.offset)
	}
	err := db.Find(res).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
}

func SelectFieldByParamBuilder(builder *ParamBuilder, res interface{}) {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	} else {
		logger.Logger.Panicf("%+v", errors.New("table not set"))
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	if builder.limit != 0 {
		db = db.Limit(builder.limit)
	}
	if builder.offset != 0 {
		db = db.Offset(builder.offset)
	}
	err := db.Scan(res).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
}

func SelectOneByCondition(obj interface{}) {
	db := database.GetDB()
	err := db.Where(obj).First(obj).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
}

func UpdateOneByCondition(obj interface{}) int64 {
	db := database.GetDB()
	tx := db.Model(obj).Updates(obj)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error updating database"))
	}
	return tx.RowsAffected
}

func UpdateBatchByParamBuilderAndMap(builder *ParamBuilder, updates map[string]interface{}) int64 {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	} else {
		logger.Logger.Panicf("%+v", errors.New("table not set"))
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	tx := db.Updates(updates)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error updating database"))
	}
	return tx.RowsAffected
}

func UpdateBatchByParamBuilder(builder *ParamBuilder, obj interface{}) int64 {
	db := database.GetDB()
	db = db.Model(obj)
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	tx := db.Updates(obj)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error updating database"))
	}
	return tx.RowsAffected
}

// InsertUpdateOne obj 必须为指针, 如果调用此方法进行update请注意需要所有属性，update条件为id
func InsertUpdateOne(obj interface{}) int64 {
	db := database.GetDB()
	tx := db.Save(obj)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error inserting database"))
	}
	return tx.RowsAffected
}

// InsertUpdateOneWithErr obj 必须为指针, 如果调用此方法进行update请注意需要所有属性，update条件为id
func InsertUpdateOneWithErr(obj interface{}) (int64, error) {
	db := database.GetDB()
	tx := db.Save(obj)
	err := tx.Error
	if err != nil {
		logger.Logger.Errorf("%+v", errors.Wrap(err, "error inserting database"))
		return 0, err
	}
	return tx.RowsAffected, nil
}

func InsertBatch(list interface{}) int64 {
	db := database.GetDB()
	tx := db.Create(list)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error inserting database"))
	}
	return tx.RowsAffected
}

func Transaction(f func()) {
	database.Trans(f, sql.LevelDefault)
}

func TransactionWithLevel(f func(), level sql.IsolationLevel) {
	database.Trans(f, level)
}

func Exec(sql string, values ...interface{}) int64 {
	db := database.GetDB()
	tx := db.Exec(sql, values...)
	err := tx.Error
	if err != nil {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error operating database"))
	}
	return tx.RowsAffected
}

func Raw(sql string, result interface{}, values ...interface{}) {
	db := database.GetDB()
	err := db.Raw(sql, values...).Find(result).Error
	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		logger.Logger.Panicf("%+v", errors.Wrap(err, "error searching database"))
	}
}

func SelectByParamStructWithError(param interface{}, res interface{}) error {
	db := database.GetDB()
	err := db.Where(param).Find(res).Error
	if err != nil {
		return err
	}
	return nil
}

func PageSelectByParamBuilderWithError(builder *ParamBuilder, res interface{}, request PageRequest) (*PageResult, error) {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	var total int64
	err := db.Count(&total).Error
	offset := (request.Page - 1) * request.PageSize
	db = db.Offset(int(offset))
	db = db.Limit(int(request.PageSize))
	err = db.Find(res).Error
	if err != nil {
		return nil, err
	}
	pages := total / int64(request.PageSize)
	if total%int64(request.PageSize) != 0 {
		pages += 1
	}
	return &PageResult{
		Total:       total,
		Pages:       int32(pages),
		PageSize:    request.PageSize,
		CurrentPage: request.Page,
		List:        res,
	}, nil
}

func SelectByParamBuilderWithError(builder *ParamBuilder, res interface{}) error {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	if builder.limit != 0 {
		db = db.Limit(builder.limit)
	}
	if builder.offset != 0 {
		db = db.Offset(builder.offset)
	}
	err := db.Find(res).Error
	if err != nil {
		return err
	}
	return nil
}

func SelectFieldByParamBuilderWithError(builder *ParamBuilder, res interface{}) error {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	} else {
		logger.Logger.Panicf("%+v", errors.New("table not set"))
	}
	if builder.selectFields != "" {
		db = db.Select(builder.selectFields)
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	if len(builder.orderByAsc) != 0 {
		for _, order := range builder.orderByAsc {
			db = db.Order(order)
		}
	}
	if len(builder.orderByDesc) != 0 {
		for _, order := range builder.orderByDesc {
			db = db.Order(fmt.Sprintf("%s desc", order))
		}
	}
	if builder.limit != 0 {
		db = db.Limit(builder.limit)
	}
	if builder.offset != 0 {
		db = db.Offset(builder.offset)
	}
	err := db.Scan(res).Error
	if err != nil {
		return err
	}
	return nil
}

func SelectOneByConditionWithError(obj interface{}) error {
	db := database.GetDB()
	err := db.Where(obj).First(obj).Error
	if err != nil {
		return err
	}
	return nil
}

func UpdateOneByConditionWithError(obj interface{}) (int64, error) {
	db := database.GetDB()
	tx := db.Model(obj).Updates(obj)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

func UpdateBatchByParamBuilderAndMapWithError(builder *ParamBuilder, updates map[string]interface{}) (int64, error) {
	db := database.GetDB()
	if builder.model != nil {
		db = db.Model(builder.model)
	} else {
		logger.Logger.Panicf("%+v", errors.New("table not set"))
	}
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	tx := db.Updates(updates)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

func UpdateBatchByParamBuilderWithError(builder *ParamBuilder, obj interface{}) (int64, error) {
	db := database.GetDB()
	db = db.Model(obj)
	setParamToDbConnection(db, builder.params)
	setOrParamToDbConnection(db, builder.orParams)
	tx := db.Updates(obj)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

// obj 必须为指针
func InsertOneWithError(obj interface{}) (int64, error) {
	db := database.GetDB()
	tx := db.Save(obj)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

func InsertBatchWithError(list interface{}) (int64, error) {
	db := database.GetDB()
	tx := db.Create(list)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

func ExecWithError(sql string, values ...interface{}) (int64, error) {
	db := database.GetDB()
	tx := db.Exec(sql, values...)
	err := tx.Error
	if err != nil {
		return 0, err
	}
	return tx.RowsAffected, nil
}

func RawWithError(sql string, result interface{}, values ...interface{}) error {
	db := database.GetDB()
	err := db.Raw(sql, values...).Find(result).Error
	if err != nil {
		return err
	}
	return nil
}
